package com.dudu.dataImport;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 同步APP应用账号与组织账号（异数据库）
 * @author gaoxs
 *
 */
public class PoolToAppUtil {
	/**
	 * http://test.cloud.yonyoutelecom.cn
	 * http://cloud.yonyoutelecom.cn
	 * 从sourceConn中t_app、t_account中根据条件导入到
	 * targetConn中的t_app_extend、t_account_extend
	 * @param sourceConn 源数据库连接
	 * @param targetConn 目的数据库连接
	 * @param appid   应用ID,多个使用逗号分隔
	 */
	public void dataConversion(Connection sourceConn,Connection targetConn,String appid){
		Statement stmt = null;
		Statement stmt_rel = null;
		MyUtil myUtil = new MyUtil();
		try{
			stmt=sourceConn.createStatement();
			/*sql 拼接*/
			StringBuffer sql=new StringBuffer();
			sql.append("select id,is_init_account,is_app_validate from t_app  where 1=1");
			if(null != appid &&  appid.length()>0){ //只导部分应用
				sql.append(" and id in ("+appid+")");
			}
			sql.append(" order by id");
			System.out.println(sql.toString());
			/*执行查询*/
			ResultSet appresult = stmt.executeQuery(sql.toString());
			/*打印总条数*/
			appresult.last();//移到最后一行   
			int rowCount = appresult.getRow(); //得到当前行号，也就是记录数   
			appresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
			System.out.println("appresultsql="+sql.toString()+",resultCount="+rowCount);
			/*遍历appresult*/
			while (appresult.next()) {
				/*获取数据*/
				String app_id = myUtil.isNull(appresult.getString("id"));
				String is_init_account=myUtil.isNull(appresult.getString("is_init_account"));//使用统一推送地址
				String is_app_validate=myUtil.isNull(appresult.getString("is_app_validate"));//使用统一验证方式
				/*创建Statement*/
				stmt_rel=sourceConn.createStatement();
				/*拼接sql*/
				String sql1="select * from t_account where category='01' and app_id="+app_id;
				/*执行查询*/
				ResultSet accountresult=stmt_rel.executeQuery(sql1);
				/*打印总条数*/
				accountresult.last();//移到最后一行   
				int rowCount1 = accountresult.getRow(); //得到当前行号，也就是记录数   
				accountresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
				System.out.println("accountresultsql="+sql1.toString()+",resultCount="+rowCount1);
				/*遍历accountresult*/
				while(accountresult.next()){
					/*获取数据*/
					String id=myUtil.isNull(accountresult.getString("id"));//ID
					String account_type=myUtil.isNull(accountresult.getString("account_type"));//账号类型
					String identifier=myUtil.isNull(accountresult.getString("identifier"));//账号标识
					String product_code=myUtil.isNull(accountresult.getString("product_code"));//外系统编码
					String key_permanent=myUtil.isNull(accountresult.getString("key_permanent"));//公钥
					String key_temp=myUtil.isNull(accountresult.getString("key_temp"));//私钥
					String linker_name=myUtil.isNull(accountresult.getString("linker_name")).replace("'", "");//联系人姓名
					String linker_phone=myUtil.isNull(accountresult.getString("linker_phone"));//手机号
					String linker_email=myUtil.isNull(accountresult.getString("linker_email"));//邮箱
					String url_ticket=myUtil.isNull(accountresult.getString("callback_url"));//话单推送地址
					String url_callstatus=myUtil.isNull(accountresult.getString("call_status_url"));//会议状态推送地址
					String url_authentication=myUtil.isNull(accountresult.getString("authentication_url"));//鉴权地址
					if(url_authentication.endsWith("queryAccountInfo")){
						url_authentication=url_authentication.replace("queryAccountInfo", "verifyProductAccount");
					}
					String url_ivrdigit=myUtil.isNull(accountresult.getString("ivrdigit_url"));//ivr按键结果推送地址
					String billing_platform=myUtil.isNull(accountresult.getString("charging_type"));//计费平台
					String create_time=myUtil.isNull(accountresult.getString("create_time"));//创建时间
					String create_user=myUtil.isNull(accountresult.getString("create_user"));//创建人
					String cust_account=myUtil.isNull(accountresult.getString("custAccount"));//计费账号
					/*创建Statement*/
					Statement stmt0=targetConn.createStatement();
					
					if(null ==account_type){//判断是否为空
						System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入账号失败，失败原因：account_type为空");
					}else if(account_type.indexOf("app")>-1){//应用账号导入
						try {
							/*判断导入时新表是否存在*/
							Boolean flag=this.isNewRow( stmt0, "t_app_extend", identifier);
							if(flag){
								/*拼接sql*/
								StringBuffer appsql=new StringBuffer();
								appsql.append("insert into t_app_extend(app_key,periphery_code,key_public,key_private,");
								appsql.append("linker_name,linker_phone,linker_email,is_init_url,is_init_validate,");
								appsql.append("url_ticket,url_callstatus,url_authentication,url_ivrdigit,");
								appsql.append("billing_platform,create_time,create_user) values ('");
								appsql.append(identifier).append("','").append(product_code).append("','").append(key_permanent).append("','").append(key_temp).append("','");
								appsql.append(linker_name).append("','").append(linker_phone).append("','").append(linker_email).append("','").append(is_init_account).append("','");
								appsql.append(is_app_validate).append("','").append(url_ticket).append("','").append(url_callstatus).append("','");
								appsql.append(url_authentication).append("','").append(url_ivrdigit).append("','").append(billing_platform).append("','").append(create_time).append("','");
								appsql.append(create_user).append("')");
								/*执行intsert命令*/
								System.out.println("instertappSQL="+appsql.toString());
								stmt0.executeUpdate(appsql.toString(),Statement.RETURN_GENERATED_KEYS);
								/*获取intsert的ID*/
								ResultSet keys = stmt0.getGeneratedKeys(); 
								if(keys.next()) {
									int appnewid=keys.getInt(1);
									System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入app账号成功 ！！新表t_app_extend对应ID="+appnewid);
								}
								targetConn.commit();
							}else{
								System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入app账号失败 ！！新表t_app_extend中存在此记录");
							}
							
						} catch (Exception e) {
							targetConn.rollback();
							targetConn.close();
							sourceConn.close();
					        stmt.close();
					        stmt_rel.close();
					        System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入app账号失败，失败原因："+e.getMessage());
							e.printStackTrace();
						}
					}else if(account_type.indexOf("company")>-1){//组织账号导入 
						try {
							if(null==cust_account || cust_account.equals("")){
								System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入company账号失败 ！！cust_account为空");
							}else{
								/*判断导入时新表是否存在*/
								Boolean flag=this.isNewRow( stmt0, "t_account_extend", cust_account);
								if(flag){
									/*拼接sql*/
									StringBuffer comsql=new StringBuffer();
									comsql.append("insert into t_account_extend (account_identify,cust_account,key_public,key_private,");
									comsql.append("linker_name,linker_phone,linker_email,");
									comsql.append("url_ticket,url_callstatus,url_authentication,url_ivrdigit,");
									comsql.append("billing_platform,create_time,create_user) values ('");
									comsql.append(identifier).append("','").append(cust_account).append("','").append(key_permanent).append("','").append(key_temp).append("','");
									comsql.append(linker_name).append("','").append(linker_phone).append("','").append(linker_email).append("','");
									comsql.append(url_ticket).append("','").append(url_callstatus).append("','");
									comsql.append(url_authentication).append("','").append(url_ivrdigit).append("','");
									comsql.append(billing_platform).append("','").append(create_time).append("','").append(create_user).append("')");
									/*执行intsert命令*/
									System.out.println("instertcomsqlSQL="+comsql.toString());
									stmt0.executeUpdate(comsql.toString(),Statement.RETURN_GENERATED_KEYS);
									/*获取intsert的ID*/
									ResultSet keys = stmt0.getGeneratedKeys(); 
									if(keys.next()) {
										int comnewid=keys.getInt(1);
										System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入company账号成功 ！！新表t_account_extend对应ID="+comnewid);
									}
									targetConn.commit();
								}else{
									System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入company账号失败 ！！新表t_account_extend中有此数据");
								}
							}							
						} catch (Exception e) {
							targetConn.rollback();
							targetConn.close();
							sourceConn.close();
					        stmt.close();
					        stmt_rel.close();
					        System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入company账号失败，失败原因："+e.getMessage());
							e.printStackTrace();
						}
					}else{
						System.out.println("正在导入id="+id+",identifier="+identifier+"时，导入账号失败，失败原因：account_type值错误(app,company),现值为："+account_type);
					}
				}
			}
			stmt.close();
		    stmt_rel.close();
		    targetConn.commit();
		    targetConn.close();
		    sourceConn.close();
		}catch(Exception e){
			try {
				targetConn.rollback();
				targetConn.close();
				sourceConn.close();
		        stmt.close();
		        stmt_rel.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	/**
	 * 判断导入时新表是否存在
	 * @param stmt0
	 * @param table
	 * @param keyvalue
	 * @return
	 * @throws Exception
	 */
	public static boolean isNewRow(Statement stmt0,String table,String keyvalue)throws Exception{
		String sql="select * from "+table;
		if(table.equals("t_app_extend")){
			sql=sql+" where app_key='"+keyvalue+"'";
		}else if(table.equals("t_account_extend")){
			sql=sql+" where cust_account='"+keyvalue+"'";
		}
		/*执行查询*/
		ResultSet accountresult=stmt0.executeQuery(sql);
		/*打印总条数*/
		accountresult.last();//移到最后一行   
		int rowCount = accountresult.getRow(); //得到当前行号，也就是记录数   
		//System.out.println("**********"+sql+"------"+rowCount);
		if(rowCount>0){
			return false;
		}else{
			return true;
		}
	}
	public static void main(String[] args) {
		/*源*/
		String sourceUrl="jdbc:mysql://223.202.31.209:3306/voip_dudu_3.0?useUnicode=true&characterEncoding=utf-8";
		String sourceUsername="root";
		String sourcePassword="heguoping123@2014";
		/*目的*/
		String targetUrl="jdbc:mysql://172.20.8.244:9596/voip_dudu?useUnicode=true&characterEncoding=utf-8";
		String targetUsername="voip_plat";
		String targetPassword="YoyoVoip@2016";
		//String appid="70,83,84";//"87,72";//应用ID 多个时使用逗号分隔87,72
		String appid="84";
		/*创建连接*/
		DBConnectionUtil dbUtil = new DBConnectionUtil();
		Connection sourceConn = dbUtil.getDBConnection(sourceUrl,sourceUsername,sourcePassword);
		Connection targetConn = dbUtil.getDBConnection(targetUrl, targetUsername, targetPassword);
		/*执行*/
		PoolToAppUtil util=new PoolToAppUtil();
		util.dataConversion(sourceConn, targetConn, appid);
		System.out.println("执行完成");
	}

}
